package wawi.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;

import wawi.actions.Artikel;
import wawi.actions.ArtikelDetails;
import wawi.actions.ArtikelUmbuchung;
import wawi.actions.Bestellung;
import wawi.actions.BestellungPosition;
import wawi.actions.Gutschrift;
import wawi.actions.GutschriftVerrechnung;
import wawi.actions.Lieferant;
import wawi.actions.Verkauf;
import wawi.actions.VerkaufPosition;
import wawi.actions.data.ArtikelAbverkauf;
import wawi.actions.data.RankingGroesse;
import wawi.kasse.KasseController;
import wawi.util.Props;

public class DbConnector
{
	static Logger l = Logger.getLogger(DbConnector.class);
	
	private static DbConnector instance;
	
	
    static
    {
        try
        {        	         	        	       		
        		Class.forName("org.firebirdsql.jdbc.FBDriver");             	
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
    }
	
	public synchronized static DbConnector getInstance()
	{
		if (instance==null)
		{
			instance = new DbConnector();
		}
		
		return instance;
	}
	
	String user = "mooi";
	String pass = "mooi";
	String url = "jdbc:firebirdsql://localhost/C:/wawi/mooi.gdb";
	
	private Connection conn;
	private int transactionCount = 0;
	
	
	
	private DbConnector()
	{		
		String propsURL = Props.getString("dbPath", url);
		System.out.println ("Using DB: " + propsURL);
		
        try
        {        	         	        	       		        	        	
        	conn = DriverManager.getConnection(propsURL, user, pass);
        	conn.setAutoCommit(true);
        	
        	DbAutomaticUpdate.execute(conn);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
	}
	
	public void disconnect()
	{
		try{
		conn.close();
		}catch(Exception e){}	
	}
	
    private void countTransaction() throws Exception
    {
//    	transactionCount++;
//            
//        if (transactionCount >= 500)
//        {           
//        	transactionCount = 0;
//        	
//        	conn.close();
//            //l.info("DB disconnected");
//                
//            try{Thread.sleep(2000);}catch(Exception e){}
//                
//            conn = DriverManager.getConnection(url, user, pass);
//            //l.info("DB reconnected");
//        }
    }
    
    
    public void setAutoCommit(boolean autoCommit) throws SQLException
    {    
    	conn.setAutoCommit(autoCommit);
    }
    
    public void commit() throws SQLException
    {
    	conn.commit();
    }
    
    public void rollback() throws SQLException
    {
    	conn.rollback();
    }
    
   /*
    * Bestellung Position
    */
    
    public synchronized List<BestellungPosition> getBestellungPositionList(int bestellung)
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<List<BestellungPosition>> rsh = new BeanListHandler<BestellungPosition>(BestellungPosition.class);

    	
    	try
    	{    		    		
    		String sql = "select bp.bestellung, bp.ean, bp.menge, bp.geliefert, bp.ekpreis from bestellung_pos as bp" +
    				" inner join artikel_details as ad on bp.ean=ad.ean where bp.bestellung=? order by ad.artnr, ad.farbe, ad.groesse asc";
    		
//    		return run.query(conn, "select * from Bestellung_pos where bestellung=? order by ean desc", rsh, bestellung);
    		return run.query(conn, sql, rsh, bestellung);
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return new ArrayList<BestellungPosition>();
    	}
    }
    
    public synchronized BestellungPosition readBestellungPosition (int bestellung, int ean)
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<BestellungPosition> rsh = new BeanHandler<BestellungPosition>(BestellungPosition.class);
    	
    	try
    	{
    		BestellungPosition ret = run.query(conn, "select * from Bestellung_pos where bestellung=? and ean=?", rsh, bestellung, ean);
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return null;
    	}	
    }    
        
    public synchronized boolean writeBestellungPosition (BestellungPosition neu)
    {    	
    	QueryRunner run = new QueryRunner();
    	
    	
    	try
    	{
			String sql = "insert into bestellung_pos (bestellung, ean, menge, geliefert, ekpreis) values (?, ?, ?, ?, ?)";
			
		    int inserts = run.update(conn, sql, neu.getBestellung(), neu.getEan(), neu.getMenge(), neu.getGeliefert(), neu.getEkpreis());
		    		    
		    System.out.println("inserts: " + inserts);		    
		    countTransaction();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
    
    public synchronized boolean updateBestellungPosition (BestellungPosition neu)
    {    	
    	QueryRunner run = new QueryRunner();
    	
    	
    	try
    	{
			String sql = "update bestellung_pos set geliefert=? where bestellung=? and ean=? ";
			
		    int inserts = run.update(conn, sql, neu.getGeliefert(), neu.getBestellung(), neu.getEan());
		    		    
		    System.out.println("updates: " + inserts);		    
		    countTransaction();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
        
    public synchronized boolean deleteBestellungPosition (BestellungPosition neu)
    {    	
    	QueryRunner run = new QueryRunner();
    	
    	
    	try
    	{
			String sql = "delete from Bestellung_pos where bestellung=? and ean=?";
			
		    int delete = run.update(conn, sql, neu.getBestellung(), neu.getEan());
		    		    
		    System.out.println("delete: " + delete);		    
		    countTransaction();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
    
    public synchronized boolean deleteBestellungPositionen (Bestellung del)
    {    	
    	QueryRunner run = new QueryRunner();
    	
    	
    	try
    	{
			String sql = "delete from Bestellung_pos where bestellung=?";
			
		    int delete = run.update(conn, sql, del.getRecid());
		    		    
		    System.out.println("delete: " + delete);		    
		    countTransaction();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
    
    
    /*
     * Bestellung
     */
    
    
    public synchronized boolean readBestellung (int recid, Bestellung dest)
    {
    	Bestellung src = readBestellung(recid);
    	
    	if (src != null)
    	{
    		try
    		{
    			BeanUtils.copyProperties(dest, src);
    			return true;
    		}
    		catch (Exception ite)
    		{
    			ite.printStackTrace();
    		}
    	}
    	
    	return false;
    }
    
    public synchronized Bestellung readBestellung (int recid)
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<Bestellung> rsh = new BeanHandler<Bestellung>(Bestellung.class);
    	
    	try
    	{
    		Bestellung ret = run.query(conn, "select * from bestellung where recid=?", rsh, recid);
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return null;
    	}	
    }
    
    public synchronized Bestellung readLastBestellung ()
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<Bestellung> rsh = new BeanHandler<Bestellung>(Bestellung.class);
    	
    	try
    	{
    		Bestellung ret = run.query(conn, "select first 1 * from bestellung ORDER BY recid DESC", rsh);
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return null;
    	}	
    }
        
    public synchronized boolean writeBestellung (Bestellung neu)
    {    	
    	QueryRunner run = new QueryRunner();
    	
    	
    	try
    	{
			String sql = "insert into bestellung (lieferant, datum, freitext, state, liefertermin, lager) values (?, ?, ?, ?, ?, ?)";
			
		    int inserts = run.update(conn, sql, neu.getLieferant(), new Date(System.currentTimeMillis()), neu.getFreitext(), neu.getState(), neu.getLiefertermin(), neu.getLager());
		    		    
		    System.out.println("inserts: " + inserts);		    
		    countTransaction();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
        
    public synchronized boolean updateBestellung (Bestellung neu)
    {    	
    	QueryRunner run = new QueryRunner();
    	
    	
    	try
    	{
			String sql = "update Bestellung set lieferant=?, freitext=?, state=?, liefertermin=?, lager=? where recid=?";
			
		    int inserts = run.update(conn, sql, neu.getLieferant(), neu.getFreitext(), neu.getState(), neu.getLiefertermin(), neu.getLager(), neu.getRecid());
		    		    
		    System.out.println("updates: " + inserts);		    
		    countTransaction();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
    
    public synchronized List<Bestellung> getBestellungList()
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<List<Bestellung>> rsh = new BeanListHandler<Bestellung>(Bestellung.class);

    	
    	try
    	{    		    		
    		return run.query(conn, "select * from Bestellung", rsh);
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return new ArrayList<Bestellung>();
    	}
    }
    
    public synchronized boolean deleteBestellung (Bestellung del)
    {
        QueryRunner run = new QueryRunner();

        try
        {
    		String sql = "delete from bestellung where recid=?";
    	
    	    int delete = run.update(conn, sql, del.getRecid());
    	    		    
    	    System.out.println("delete: " + delete);		    
    	    countTransaction();
        	
        	return true;
        }
        catch (Exception e)
        {
        	e.printStackTrace();
        	return false;
        }
    }
    
    public synchronized List<Bestellung> getBestellungenOfLiefereantFuerLager(int liefereant, int lager, boolean load)
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<List<Bestellung>> rsh = new BeanListHandler<Bestellung>(Bestellung.class);

    	
    	try
    	{    		    		
    		List<Bestellung> ret =  run.query(conn, "select * from Bestellung where lieferant=? and (state=1 or state=2) and lager=?", rsh, liefereant, lager);
    		
    		for (Bestellung tmp : ret)
    		{
    			if (load)
    				tmp.loadPositions();
    		}
    		
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return new ArrayList<Bestellung>();
    	}
    }
    
    
    
    
    
    /*
     * Lieferant
     */
    
    
    public synchronized boolean readLieferant (int recid, Lieferant dest)
    {
    	Lieferant src = readLieferant(recid);
    	
    	if (src != null)
    	{
    		try
    		{
    			BeanUtils.copyProperties(dest, src);
    			return true;
    		}
    		catch (Exception ite)
    		{
    			ite.printStackTrace();
    		}
    	}
    	
    	return false;
    }
    
    public synchronized Lieferant readLieferant (int recid)
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<Lieferant> rsh = new BeanHandler<Lieferant>(Lieferant.class);
    	
    	try
    	{
    		Lieferant ret = run.query(conn, "select * from lieferant where recid=?", rsh, recid);
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return null;
    	}	
    }    
    
    public synchronized boolean writeLieferant (Lieferant neu)
    {    	
    	QueryRunner run = new QueryRunner();
    	
    	
    	try
    	{
			String sql = "insert into lieferant (firma, adresse, telefon, mail) values (?, ?, ?, ?)";
			
		    int inserts = run.update(conn, sql, neu.getFirma(), neu.getAdresse(), neu.getTelefon(), neu.getMail());
		    		    
		    System.out.println("inserts: " + inserts);		    
		    countTransaction();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
    
    public synchronized boolean updateLieferant (Lieferant neu)
    {    	
    	QueryRunner run = new QueryRunner();
    	
    	
    	try
    	{
			String sql = "update lieferant set firma=?, adresse=?, telefon=?, mail=? where recid=?";
			
		    int inserts = run.update(conn, sql, neu.getFirma(), neu.getAdresse(), neu.getTelefon(), neu.getMail(), neu.getRecid());
		    		    
		    System.out.println("updates: " + inserts);		    
		    countTransaction();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
    
    public synchronized List<Lieferant> getLieferantenList()
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<List<Lieferant>> rsh = new BeanListHandler<Lieferant>(Lieferant.class);

    	
    	try
    	{    		    		
    		return run.query(conn, "select * from Lieferant", rsh);
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return new ArrayList<Lieferant>();
    	}
    }
    
    public synchronized Lieferant getLieferant (int recid)
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<Lieferant> rsh = new BeanHandler<Lieferant>(Lieferant.class);
    	
    	try
    	{
    		Lieferant ret = run.query(conn, "select * from lieferant where recid=?", rsh, recid);
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return null;
    	}
    }
    
    
    
    
    
    
    /*
     * Artikel
     */
    
    
    public synchronized boolean existsBestand (int ean, int lager)
    {
    	try
    	{
    		String sql = String.format("select * from artikel_bestand where ean=%d and lager=%d", ean, lager);
    		
    		Statement stmt = conn.createStatement();
    		ResultSet rs = stmt.executeQuery(sql);
    		
    		boolean ret = false;
    		
    		if (rs.next())
    		{
    			ret = true;
    		}
    		
    		rs.close();
    		stmt.close();
    		
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
    
    public synchronized boolean updateBestand (int ean, int lager, int bestand)
    {    	
    	QueryRunner run = new QueryRunner();
    	
    	
    	try
    	{
			String sql = "update artikel_bestand set bestand=? where lager=? and ean=? ";
			
		    int inserts = run.update(conn, sql, bestand, lager, ean);
		    		    
		    System.out.println("updates: " + inserts);		    
		    countTransaction();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
    
    public synchronized boolean insertBestand (int ean, int lager, int bestand)
    {    	
    	QueryRunner run = new QueryRunner();
    	
    	
    	try
    	{
			String sql = "insert into artikel_bestand (ean, lager, bestand) values (?, ?, ?)";
			
		    int inserts = run.update(conn, sql, ean, lager, bestand);
		    		    
		    System.out.println("inserts: " + inserts);		    
		    countTransaction();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }

    public synchronized boolean insertOrUpdateBestand (int ean, int lager, int bestand)
    {
    	if (existsBestand(ean, lager))
    		return updateBestand(ean, lager, bestand);
    	else
    		return insertBestand(ean, lager, bestand);
    }
    
    
	public synchronized boolean writeArtikelDetails (ArtikelDetails neu)
	{
		QueryRunner run = new QueryRunner();
		
		try
		{
			String sql = "insert into artikel_details (ean, artnr, farbe, groesse) values (?, ?, ?, ?)";
			
		    int inserts = run.update(conn, sql, neu.getEan(), neu.getArtNr(), neu.getFarbe(), neu.getGroesse());
		    		    
		    System.out.println("inserts: " + inserts);		    
		    countTransaction();
		    
		    return true;
		}
		catch(Exception sqle)
		{
		    sqle.printStackTrace();
		    
		    return false;
		}				
	}
	
	public synchronized boolean updateArtikelDetails (ArtikelDetails neu)
	{
		QueryRunner run = new QueryRunner();
		
		try
		{
			String sql = "update artikel_details set farbe=?, groesse=? where ean=?";
			
		    int inserts = run.update(conn, sql, neu.getFarbe(), neu.getGroesse(), neu.getEan());
		    		    
		    System.out.println("updates: " + inserts);		    
		    countTransaction();
		    
		    return true;
		}
		catch(Exception sqle)
		{
		    sqle.printStackTrace();
		    
		    return false;
		}				
	}
    
	
	public ArtikelDetails getArtikelDetailsWithScannedEan (String scannedEAN)
	{
		//Inhouse EAN beginnt mit 2
		if (!scannedEAN.startsWith("2"))
			return null;
		else
		{
			String myEAN = KasseController.cutEAN(scannedEAN);
			l.info(String.format("EAN scanned: %s  ==>  %s", scannedEAN, myEAN));						
			
			try
			{
				int ean = Integer.parseInt(myEAN);		
				return getArtikelDetailsWithEan(ean);
			}
			catch (NumberFormatException nfe)
			{
				return null;
			}
		}
	}
	
    public synchronized ArtikelDetails getArtikelDetailsWithEan (int ean)
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<ArtikelDetails> rsh = new BeanHandler<ArtikelDetails>(ArtikelDetails.class);
    	
    	try
    	{
    		ArtikelDetails ret = run.query(conn, "select * from artikel_details where ean=?", rsh, ean);
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return null;
    	}
    }
    
    public synchronized boolean readArtikelDetails (int ean, ArtikelDetails dest)
    {
    	ArtikelDetails src = DbConnector.getInstance().getArtikelDetailsWithEan(ean);
    	
    	if (src != null)
    	{
    		try
    		{
    			BeanUtils.copyProperties(dest, src);
    			return true;
    		}
    		catch (Exception ite)
    		{
    			ite.printStackTrace();
    		}
    	}
    	
    	return false;
    }
    
    public synchronized List<ArtikelDetails> getArtikelDetailsWithArtNr (int artnr)
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<List<ArtikelDetails>> rsh = new BeanListHandler<ArtikelDetails>(ArtikelDetails.class);

    	
    	try
    	{    		    		
    		return run.query(conn, "select * from artikel_details where artnr=?", rsh, artnr);
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return new ArrayList<ArtikelDetails>();
    	}
    }
    
    public synchronized List<String> getMoegliche (int artnr, String column)
    {
    	List<String> ret = new ArrayList<String>();
    	
    	try
    	{
    		String sql = String.format("select %s from artikel_details where artnr=%d order by %s", column, artnr, column);
    		
    		Statement stmt = conn.createStatement();
    		ResultSet rs = stmt.executeQuery(sql);
    		
    		String last = null;
    		
    		while (rs.next())
    		{
    			String val = rs.getString(column);
    			
    			if (!val.equals(last))
    			{
    				ret.add(val);
    				last = val;
    			}
    		}
    		
    		rs.close();
    		stmt.close();
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    	}
    	
    	return ret;
    }

    public synchronized int eanForKombination (String farbe, String groesse, int artnr)
    {    
    	try
    	{
    		String sql = String.format("select * from artikel_details where farbe='%s' and groesse='%s' and artnr=%d", farbe, groesse, artnr);
    		
    		Statement stmt = conn.createStatement();
    		ResultSet rs = stmt.executeQuery(sql);
    		
    		int ret = -1;
    		
    		if (rs.next())
    		{
    			ret = rs.getInt("ean");
    		}
    		
    		rs.close();
    		stmt.close();
    		
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return -1;
    	}
    }
    
    public synchronized int bestandsabfrage (int ean, int lager)
    {
    	try
    	{
    		String sql = String.format("select * from artikel_bestand where ean=%d and lager=%d", ean, lager);
    		
    		Statement stmt = conn.createStatement();
    		ResultSet rs = stmt.executeQuery(sql);
    		
    		int ret = 0;
    		
    		if (rs.next())
    		{
    			ret = rs.getInt("bestand");
    		}
    		
    		rs.close();
    		stmt.close();
    		
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return -1;
    	}
    }

    public synchronized Artikel getArtikelWithNr (int artNr)
    {
    	Artikel ret = new Artikel();
    	
    	if (readArtikelWithNr(artNr, ret))
    		return ret;
    	else
    		return null;
    }
    
    public synchronized boolean readArtikelWithNr (int artNr, Artikel artikel)
    {
    	try
    	{
    		String qry = String.format("select * from artikel where artnr=%d", artNr);
    		Statement stmt = conn.createStatement();
    		ResultSet rs = stmt.executeQuery(qry);
    	
    		boolean ret = false;
    		
    		if (rs.next())
    		{    			
    			readArtikelFromResultSet(rs, artikel);
    			ret = true;
    		}
    		
    		rs.close();
    		stmt.close();
    		
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    	}
    	
    	return false;
    }
    
    public synchronized ArrayList<Artikel> getArtikel()
    {
    	ArrayList<Artikel> ret = new ArrayList<Artikel>();
    	
    	try
    	{
    		Statement stmt = conn.createStatement();
    		ResultSet rs = stmt.executeQuery("select * from artikel");
    		
    		while (rs.next())
    		{
    			Artikel tmp = new Artikel();
    			readArtikelFromResultSet(rs, tmp);
    			ret.add(tmp);
    		}
    		
    		rs.close();
    		stmt.close();
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    	}
    	
    	return ret;
    }

	private void readArtikelFromResultSet(ResultSet rs, Artikel tmp)
			throws SQLException
	{		
		tmp.setArtNr(rs.getInt("artnr"));    			
		tmp.setHersteller(rs.getString("hersteller"));
		tmp.setTextKurz(rs.getString("textkurz"));
		tmp.setTextLang(rs.getString("textlang"));
		tmp.setPreisVK(rs.getFloat("preis_vk"));
		tmp.setPreisEK(rs.getFloat("preis_ek"));
		tmp.setKalkulation(rs.getString("kalkulation"));
		tmp.setKategorie(rs.getInt("kategorie"));
	}
    
	
	public synchronized boolean writeArtikel (Artikel neu)
	{
		QueryRunner run = new QueryRunner();
		
		try
		{
			String sql = "insert into artikel (artnr, hersteller, " +
    				"textkurz, textlang, preis_ek, preis_vk, kalkulation, kategorie) values (?, ?, ?, ?, ?, ?, ?, ?)";
			
		    int inserts = run.update(conn, sql,
		    		neu.getArtNr(), neu.getHersteller(), neu.getTextKurz(), neu.getTextLang(),
		    		neu.getPreisEK(), neu.getPreisVK(), neu.getKalkulation(), neu.getKategorie());
		    
		    System.out.println("inserts: " + inserts);
		}
		catch(SQLException sqle)
		{
		    sqle.printStackTrace();
		}

		
		return true;
	}

    
    public synchronized boolean updateArtikel (Artikel a)
    {
    	try
    	{
    		PreparedStatement stmt = conn.prepareStatement("update artikel set hersteller=?, " +
    				"textkurz=?, textlang=?, preis_ek=?, preis_vk=?, kalkulation=?, kategorie=? where artNr=?");
    		
    		stmt.setString(1, a.getHersteller());
    		stmt.setString(2, a.getTextKurz());
    		stmt.setString(3, a.getTextLang());
    		stmt.setFloat(4, a.getPreisEK());
    		stmt.setFloat(5, a.getPreisVK());
    		stmt.setString(6, a.getKalkulation());
    		stmt.setInt(7, a.getKategorie());
    		stmt.setInt(8, a.getArtNr());
    		
    		stmt.executeUpdate();
    		stmt.close();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
    
    
    
    
    
    /*
     * Verkauf
     */
    
    
    public synchronized boolean readVerkauf (int recid, Verkauf dest)
    {
    	Verkauf src = readVerkauf(recid);
    	
    	if (src != null)
    	{
    		try
    		{
    			BeanUtils.copyProperties(dest, src);
    			return true;
    		}
    		catch (Exception ite)
    		{
    			ite.printStackTrace();
    		}
    	}
    	
    	return false;
    }
           
    public synchronized Verkauf readVerkauf (int recid)
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<Verkauf> rsh = new BeanHandler<Verkauf>(Verkauf.class);
    	
    	try
    	{
    		Verkauf ret = run.query(conn, "select * from Verkauf where recid=?", rsh, recid);
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return null;
    	}	
    }    
    
    public synchronized Verkauf readLastVerkauf()
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<Verkauf> rsh = new BeanHandler<Verkauf>(Verkauf.class);
    	
    	try
    	{
    		Verkauf ret = run.query(conn, "select first 1 * from Verkauf ORDER BY recid DESC", rsh);
    		return ret;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return null;
    	}	
    }
    
    public synchronized boolean writeVerkauf (Verkauf neu)
    {    	
    	QueryRunner run = new QueryRunner();
    	
    	
    	try
    	{
			String sql = "insert into Verkauf (datum, laden, verkaeufer, status, zahlart, kreditkarte) values (?, ?, ?, ?, ?, ?)";
			
		    int inserts = run.update(conn, sql, neu.getDatum(), neu.getLaden(), neu.getVerkaeufer(), neu.getStatus(), neu.getZahlart(), neu.getKreditkarte());
		    		    
		    System.out.println("inserts: " + inserts);		    
		    countTransaction();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
    
    public synchronized boolean updateVerkauf (Verkauf neu)
    {    	
    	QueryRunner run = new QueryRunner();
    	
    	
    	try
    	{
			String sql = "update Verkauf set datum=?, laden=?, verkaeufer=?, status=?, zahlart=?, kreditkarte=?, hinweis=? where recid=?";
			
		    int inserts = run.update(conn, sql, neu.getDatum(), neu.getLaden(), neu.getVerkaeufer(), neu.getStatus(), neu.getZahlart(), neu.getKreditkarte(), neu.getHinweis(), neu.getRecid());
		    		    
		    System.out.println("updates: " + inserts);		    
		    countTransaction();
    		
    		return true;
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return false;
    	}
    }
    
    public synchronized List<Verkauf> getVerkaufList()
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<List<Verkauf>> rsh = new BeanListHandler<Verkauf>(Verkauf.class);

    	
    	try
    	{    		    		
    		return run.query(conn, "select * from Verkauf order by recid desc", rsh);
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return new ArrayList<Verkauf>();
    	}
    }
    
    public synchronized List<Verkauf> getVerkaufListHeute(int filiale)
    {
    	QueryRunner run = new QueryRunner();
    	ResultSetHandler<List<Verkauf>> rsh = new BeanListHandler<Verkauf>(Verkauf.class);

    	
    	try
    	{    		  
    		GregorianCalendar cal = new GregorianCalendar();
    		int day = cal.get(Calendar.DAY_OF_MONTH);
    		int month = cal.get(Calendar.MONTH) +1;
    		int year = cal.get(Calendar.YEAR);
    		
    		String select = String.format("SELECT * FROM verkauf WHERE EXTRACT(DAY FROM datum) = %d AND EXTRACT(MONTH FROM datum) = %d AND EXTRACT(YEAR FROM datum) = %d and laden=%d  order by recid", day, month, year, filiale);    		
    		return run.query(conn, select, rsh);
    	}
    	catch (Exception e)
    	{
    		e.printStackTrace();
    		return new ArrayList<Verkauf>();
    	}
    }
    
    public synchronized boolean deleteVerkauf (Verkauf del)
    {
        QueryRunner run = new QueryRunner();

        try
        {
    		String sql = "delete from verkauf where recid=?";
    	
    	    int delete = run.update(conn, sql, del.getRecid());
    	    		    
    	    System.out.println("delete: " + delete);		    
    	    countTransaction();
        	
        	return true;
        }
        catch (Exception e)
        {
        	e.printStackTrace();
        	return false;
        }
    }
    
    
    /*
     * Verkauf Position
     */
     
     public synchronized List<VerkaufPosition> getVerkaufPositionList(int verkauf)
     {
     	QueryRunner run = new QueryRunner();
     	ResultSetHandler<List<VerkaufPosition>> rsh = new BeanListHandler<VerkaufPosition>(VerkaufPosition.class);

     	
     	try
     	{    		    		
     		return run.query(conn, "select * from verkauf_pos where verkauf=? order by ean desc", rsh, verkauf);
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return new ArrayList<VerkaufPosition>();
     	}
     }
     
     public synchronized VerkaufPosition readVerkaufPosition (int verkauf, int ean, int status)
     {
     	QueryRunner run = new QueryRunner();
     	ResultSetHandler<VerkaufPosition> rsh = new BeanHandler<VerkaufPosition>(VerkaufPosition.class);
     	
     	try
     	{
     		VerkaufPosition ret = run.query(conn, "select * from verkauf_pos where verkauf=? and ean=? and status=?", rsh, verkauf, ean, status);
     		return ret;
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return null;
     	}	
     }    
         
     public synchronized boolean writeVerkaufPosition (VerkaufPosition neu)
     {    	
     	QueryRunner run = new QueryRunner();
     	     	
     	try
     	{
 			String sql = "insert into verkauf_pos (verkauf, ean, preisvk, menge, status, preisangepasst) values (?, ?, ?, ?, ?, ?)";
 			
 		    int inserts = run.update(conn, sql, neu.getVerkauf(), neu.getEan(), neu.getPreisvk(), neu.getMenge(), neu.getStatus(), neu.getPreisangepasst());
 		    		    
 		    System.out.println("inserts: " + inserts);		    
 		    countTransaction();
     		
     		return true;
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return false;
     	}
     }
     
     public synchronized boolean updateVerkaufPosition (VerkaufPosition neu)
     {    	
     	QueryRunner run = new QueryRunner();
     	
     	
     	try
     	{
 			String sql = "update verkauf_pos set menge=?, preisvk=?, status=?, preisangepasst=? where verkauf=? and ean=? ";
 			
 		    int inserts = run.update(conn, sql, neu.getMenge(), neu.getPreisvk(), neu.getStatus(), neu.getPreisangepasst(), neu.getVerkauf(), neu.getEan());
 		    		    
 		    System.out.println("updates: " + inserts);		    
 		    countTransaction();
     		
     		return true;
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return false;
     	}
     }
         
     public synchronized boolean deleteVerkaufPosition (VerkaufPosition neu)
     {    	
     	QueryRunner run = new QueryRunner();
     	
     	
     	try
     	{
 			String sql = "delete from verkauf_pos where verkauf=? and ean=?";
 			
 		    int delete = run.update(conn, sql, neu.getVerkauf(), neu.getEan());
 		    		    
 		    System.out.println("delete: " + delete);		    
 		    countTransaction();
     		
     		return true;
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return false;
     	}
     }
     
     public synchronized boolean deleteVerkaufPositionen (Verkauf del)
     {    	
     	QueryRunner run = new QueryRunner();
     	
     	
     	try
     	{
 			String sql = "delete from verkauf_pos where verkauf=?";
 			
 		    int delete = run.update(conn, sql, del.getRecid());
 		    		    
 		    System.out.println("delete: " + delete);		    
 		    countTransaction();
     		
     		return true;
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return false;
     	}
     }
    
     
     
     public synchronized boolean journal_wareneingang (Bestellung bestellung, ArtikelDetails ad, int lager, int menge)
     {
      	QueryRunner run = new QueryRunner();
	     	
      	try
      	{
  			String sql = "insert into jrn_wareneingang (datum, lager, bestellung, ean, menge) values (?, ?, ?, ?, ?)";
  			
  		    int inserts = run.update(conn, sql, new Date(System.currentTimeMillis()), lager, bestellung.getRecid(), ad.getEan(), menge);
  		    		    
  		    System.out.println("inserts: " + inserts);		    
  		    countTransaction();
      		
      		return true;
      	}
      	catch (Exception e)
      	{
      		e.printStackTrace();
      		return false;
      	}
     }
     
     
     public synchronized boolean writeArtikelUmbuchung (ArtikelUmbuchung neu)
     {    	
     	QueryRunner run = new QueryRunner();
     	
     	
     	try
     	{
 			String sql = "insert into ARTIKEL_UMBUCHUNG (ean, status, von, nach, datum_start) values (?, ?, ?, ?, ?)";
 			
 		    int inserts = run.update(conn, sql, neu.getEan(), neu.getStatus(), neu.getVon(),
 		    		neu.getNach(), new Date(System.currentTimeMillis()));
 		    		    
 		    System.out.println("inserts: " + inserts);		    
 		    countTransaction();
     		
     		return true;
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return false;
     	}
     }
     
     public synchronized boolean updateArtikelUmbuchung (ArtikelUmbuchung neu)
     {    	
     	QueryRunner run = new QueryRunner();
     	
     	
     	try
     	{
 			String sql = "update ARTIKEL_UMBUCHUNG set status=?, datum_ende=? where recid=?";
 			
 		    int inserts = run.update(conn, sql, neu.getStatus(), neu.getDatum_ende(), neu.getRecid());
 		    		    
 		    System.out.println("updates: " + inserts);		    
 		    countTransaction();
     		
     		return true;
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return false;
     	}
     }
     
     public synchronized boolean deleteArtikelUmbuchung (ArtikelUmbuchung del)
     {    	
     	QueryRunner run = new QueryRunner();
     	
     	
     	try
     	{
 			String sql = "delete from ARTIKEL_UMBUCHUNG where recid=?";
 			
 		    int inserts = run.update(conn, sql, del.getRecid());
 		    		    
 		    System.out.println("delete: " + inserts);		    
 		    countTransaction();
     		
     		return true;
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return false;
     	}
     }
     
     public synchronized List<ArtikelUmbuchung> getArtikelUmbuchungListVON(int von)
     {
     	QueryRunner run = new QueryRunner();
     	ResultSetHandler<List<ArtikelUmbuchung>> rsh = new BeanListHandler<ArtikelUmbuchung>(ArtikelUmbuchung.class);

     	
     	try
     	{    		    		
     		return run.query(conn, "select * from Artikel_Umbuchung where von=? and status=? order by nach", rsh, von, ArtikelUmbuchung.STATUS_IM_VERSAND);
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return new ArrayList<ArtikelUmbuchung>();
     	}
     }
     
     public synchronized List<ArtikelUmbuchung> getArtikelUmbuchungListNACH(int nach)
     {
     	QueryRunner run = new QueryRunner();
     	ResultSetHandler<List<ArtikelUmbuchung>> rsh = new BeanListHandler<ArtikelUmbuchung>(ArtikelUmbuchung.class);

     	
     	try
     	{    		    		
     		return run.query(conn, "select * from Artikel_Umbuchung where nach=? and status=? order by von", rsh, nach, ArtikelUmbuchung.STATUS_IM_VERSAND);
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return new ArrayList<ArtikelUmbuchung>();
     	}
     }
     
     public synchronized List<ArtikelUmbuchung> getArtikelUmbuchungListNACH_EAN(int nach, int ean)
     {
     	QueryRunner run = new QueryRunner();
     	ResultSetHandler<List<ArtikelUmbuchung>> rsh = new BeanListHandler<ArtikelUmbuchung>(ArtikelUmbuchung.class);

     	
     	try
     	{    		    		
     		return run.query(conn, "select * from Artikel_Umbuchung where nach=? and ean=? and status=?", rsh, nach, ean, ArtikelUmbuchung.STATUS_IM_VERSAND);
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return new ArrayList<ArtikelUmbuchung>();
     	}
     }
     
     public synchronized List<ArtikelUmbuchung> getArtikelUmbuchungList_VON_NACH_EAN(int von, int nach, int ean)
     {
     	QueryRunner run = new QueryRunner();
     	ResultSetHandler<List<ArtikelUmbuchung>> rsh = new BeanListHandler<ArtikelUmbuchung>(ArtikelUmbuchung.class);

     	
     	try
     	{    		    		
     		return run.query(conn, "select * from Artikel_Umbuchung where von=? and nach=? and ean=? and status=?", rsh, von, nach, ean, ArtikelUmbuchung.STATUS_IM_VERSAND);
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return new ArrayList<ArtikelUmbuchung>();
     	}
     }
     
     
     
     
     /*
      * Gutschrift
      */
     
     
     public synchronized boolean readGutschrift (int recid, Gutschrift dest)
     {
    	 Gutschrift src = readGutschrift(recid);
     	
     	if (src != null)
     	{
     		try
     		{
     			BeanUtils.copyProperties(dest, src);
     			return true;
     		}
     		catch (Exception ite)
     		{
     			ite.printStackTrace();
     		}
     	}
     	
     	return false;
     }
            
     public synchronized Gutschrift readGutschrift (int recid)
     {
     	QueryRunner run = new QueryRunner();
     	ResultSetHandler<Gutschrift> rsh = new BeanHandler<Gutschrift>(Gutschrift.class);
     	
     	try
     	{
     		Gutschrift ret = run.query(conn, "select * from Gutschrift where recid=?", rsh, recid);
     		return ret;
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return null;
     	}	
     }    
     
     public synchronized Gutschrift readLastGutschrift()
     {
     	QueryRunner run = new QueryRunner();
     	ResultSetHandler<Gutschrift> rsh = new BeanHandler<Gutschrift>(Gutschrift.class);
     	
     	try
     	{
     		Gutschrift ret = run.query(conn, "select first 1 * from Gutschrift ORDER BY recid DESC", rsh);
     		return ret;
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return null;
     	}	
     }
     
     public synchronized boolean writeGutschrift (Gutschrift neu)
     {    	
     	QueryRunner run = new QueryRunner();
    	
     	try
     	{
 			String sql = "insert into Gutschrift (datum, status, betrag, laden, hinweis) values (?, ?, ?, ?, ?)";
 			
 		    int inserts = run.update(conn, sql, neu.getDatum(), neu.getStatus(), neu.getBetrag(), neu.getLaden(), neu.getHinweis());
 		    		    
 		    System.out.println("inserts: " + inserts);		    
 		    countTransaction();
     		
     		return true;
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return false;
     	}
     }
     
     public synchronized boolean updateGutschrift (Gutschrift neu)
     {    	
     	QueryRunner run = new QueryRunner();
     	
     	
     	try
     	{
 			String sql = "update Gutschrift set datum=?, laden=?, status=?, betrag=?, hinweis=? where recid=?";
 			
 		    int inserts = run.update(conn, sql, neu.getDatum(), neu.getLaden(), neu.getStatus(), neu.getBetrag(), neu.getHinweis(), neu.getRecid());
 		    		    
 		    System.out.println("updates: " + inserts);		    
 		    countTransaction();
     		
     		return true;
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return false;
     	}
     }
     
     public synchronized List<Gutschrift> getGutschriftList()
     {
     	QueryRunner run = new QueryRunner();
     	ResultSetHandler<List<Gutschrift>> rsh = new BeanListHandler<Gutschrift>(Gutschrift.class);

     	
     	try
     	{    		    		
     		return run.query(conn, "select * from Gutschrift order by recid", rsh);
     	}
     	catch (Exception e)
     	{
     		e.printStackTrace();
     		return new ArrayList<Gutschrift>();
     	}
     }
     
     public synchronized boolean deleteGutschrift (Gutschrift del)
     {
         QueryRunner run = new QueryRunner();

         try
         {
     		String sql = "delete from Gutschrift where recid=?";
     	
     	    int delete = run.update(conn, sql, del.getRecid());
     	    		    
     	    System.out.println("delete: " + delete);		    
     	    countTransaction();
         	
         	return true;
         }
         catch (Exception e)
         {
         	e.printStackTrace();
         	return false;
         }
     }
     
 	public Gutschrift getGutschriftWithScannedEan (String scannedEAN)
 	{
 		//EAN-99 beginnt mit 99
 		if (!scannedEAN.startsWith("99"))
 			return null;
 		else
 		{
 			String myEAN = scannedEAN.substring(2, scannedEAN.length()-1);
 			l.info(String.format("Gutschrift-EAN scanned: %s  ==>  %s", scannedEAN, myEAN));						
 			
 			try
 			{
 				int guNr = Integer.parseInt(myEAN);		
 				return readGutschrift(guNr);
 			}
 			catch (NumberFormatException nfe)
 			{
 				return null;
 			}
 		}
 	}
     
     
     public synchronized boolean jrn_artikelStorno (Verkauf verkauf, int ean, int menge, int lager)
     {
      	QueryRunner run = new QueryRunner();
	     	
      	try
      	{
  			String sql = "insert into JRN_ARTIKEL_STORNO (datum, verkauf, ean, menge, lager) values (?, ?, ?, ?, ?)";
  			
  		    int inserts = run.update(conn, sql, new Date(System.currentTimeMillis()), verkauf.getRecid(), ean, menge, lager);
  		    		    
  		    System.out.println("inserts: " + inserts);		    
  		    countTransaction();
      		
      		return true;
      	}
      	catch (Exception e)
      	{
      		e.printStackTrace();
      		return false;
      	}
     }
     
     
     
     
     /*
      * GutschriftVerrechnung
      */
      
      public synchronized List<GutschriftVerrechnung> getGutschriftVerrechnungList(int verkauf)
      {
      	QueryRunner run = new QueryRunner();
      	ResultSetHandler<List<GutschriftVerrechnung>> rsh = new BeanListHandler<GutschriftVerrechnung>(GutschriftVerrechnung.class);

      	
      	try
      	{    		    		
      		return run.query(conn, "select * from GUTSCHRIFT_VERRECHNUNG where verkauf=?", rsh, verkauf);
      	}
      	catch (Exception e)
      	{
      		e.printStackTrace();
      		return new ArrayList<GutschriftVerrechnung>();
      	}
      }
      
      public synchronized GutschriftVerrechnung readGutschriftVerrechnung (int verkauf, int gutschrift)
      {
      	QueryRunner run = new QueryRunner();
      	ResultSetHandler<GutschriftVerrechnung> rsh = new BeanHandler<GutschriftVerrechnung>(GutschriftVerrechnung.class);
      	
      	try
      	{
      		GutschriftVerrechnung ret = run.query(conn, "select * from GUTSCHRIFT_VERRECHNUNG where verkauf=? and gutschrift=?", rsh, verkauf, gutschrift);
      		return ret;
      	}
      	catch (Exception e)
      	{
      		e.printStackTrace();
      		return null;
      	}	
      }    
          
      public synchronized boolean writeGutschriftVerrechnung (GutschriftVerrechnung neu)
      {    	
      	QueryRunner run = new QueryRunner();
      	     	
      	try
      	{
  			String sql = "insert into GUTSCHRIFT_VERRECHNUNG (verkauf, gutschrift, datum) values (?, ?, ?)";
  			
  		    int inserts = run.update(conn, sql, neu.getVerkauf(), neu.getGutschrift(), neu.getVerrechnet());
  		    		    
  		    System.out.println("inserts: " + inserts);		    
  		    countTransaction();
      		
      		return true;
      	}
      	catch (Exception e)
      	{
      		e.printStackTrace();
      		return false;
      	}
      }
      
          
      public synchronized boolean deleteGutschriftVerrechnung (GutschriftVerrechnung neu)
      {    	
      	QueryRunner run = new QueryRunner();
      	
      	
      	try
      	{
  			String sql = "delete from GUTSCHRIFT_VERRECHNUNG where verkauf=? and gutschrift=?";
  			
  		    int delete = run.update(conn, sql, neu.getVerkauf(), neu.getGutschrift());
  		    		    
  		    System.out.println("delete: " + delete);		    
  		    countTransaction();
      		
      		return true;
      	}
      	catch (Exception e)
      	{
      		e.printStackTrace();
      		return false;
      	}
      }
      
      public synchronized boolean deleteGutschriftVerrechnungen (Verkauf del)
      {    	
      	QueryRunner run = new QueryRunner();
      	
      	
      	try
      	{
  			String sql = "delete from GUTSCHRIFT_VERRECHNUNG where verkauf=?";
  			
  		    int delete = run.update(conn, sql, del.getRecid());
  		    		    
  		    System.out.println("delete: " + delete);		    
  		    countTransaction();
      		
      		return true;
      	}
      	catch (Exception e)
      	{
      		e.printStackTrace();
      		return false;
      	}
      }
     
     
      
      public synchronized List<ArtikelAbverkauf> auswertungArtikelAbverkauf()
      {
      	QueryRunner run = new QueryRunner();
      	ResultSetHandler<List<ArtikelAbverkauf>> rsh = new BeanListHandler<ArtikelAbverkauf>(ArtikelAbverkauf.class);

      	
      	try
      	{    		    		
      		return run.query(conn, "select artnr, hersteller, textkurz, sum(bestellt) as bestellt, sum(geliefert) as geliefert, sum(verkauft) as verkauft from(select l.artnr, l.hersteller, l.textkurz, l.ean, l.BESTELLT, l.GELIEFERT, r.verkauft from (select artnr, hersteller, textkurz, bestellmenge.ean, BESTELLT, GELIEFERT from(select ean, SUM(menge) as BESTELLT, SUM(geliefert) AS GELIEFERT from bestellung_pos group by ean) bestellmenge join (select a.artnr, a.hersteller, a.textkurz, ad.ean from artikel a join artikel_details ad on a.artnr=ad.artnr) artikelDaten on bestellmenge.ean=artikelDaten.ean) l left outer join (select ean, SUM(menge) as verkauft from verkauf_pos group by ean) r on l.ean = r.ean) group by artnr, hersteller, textkurz order by artnr", rsh);
      	}
      	catch (Exception e)
      	{
      		e.printStackTrace();
      		return new ArrayList<ArtikelAbverkauf>();
      	}
      }
      
      public synchronized List<RankingGroesse> auswertungRankingGroesse()
      {
      	QueryRunner run = new QueryRunner();
      	ResultSetHandler<List<RankingGroesse>> rsh = new BeanListHandler<RankingGroesse>(RankingGroesse.class);

      	
      	try
      	{    		    		
      		return run.query(conn, "select sum(verkauft) as cntGroesse, groesse from (select vp.ean, vp.verkauft, ad.groesse, ad.artnr from (select ean, sum(menge) as verkauft from verkauf_pos group by ean) vp join artikel_details ad on vp.ean=ad.ean) group by groesse order by cntGroesse desc", rsh);
      	}
      	catch (Exception e)
      	{
      		e.printStackTrace();
      		return new ArrayList<RankingGroesse>();
      	}
      }
}
